//
// Created by henry on 2025/11/7.
//

#include "database.h"

//定义数据库句柄
sqlite3 * db;
//打开数据库
int open_db(){
    int rc = sqlite3_open("BMIS.db",&db);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"open_db:创建数据库失败:%s\n", sqlite3_errmsg(db));
        return -1;
    }
//    printf("open_db:创建数据库成功!\n");
    return 0;
}
//初始数据库，建表
int init_db(){
    //打开数据库
    if(open_db()!=SQLITE_OK){
        return -1;
    }
    //建表
    if(create_front_user()!=0){
        return -1;
    }

    if(create_admin_user()!=0){
        return -1;
    }

    if(create_book_category()!=0){
        return -1;
    }

    if(create_book()!=0){
        return -1;
    }
    if(creat_borrow_record()!=0){
        return -1;
    }
//    printf("init_db:数据库初始化完成!\n");
    return 0;
}

//创建前台用户表
int create_front_user(){
//    const char * drop =
//            "drop table front_user;";
    //定义建表语句
    const char * sql =
//            "drop table if EXISTS front_user;"
            "create table if not exists front_user"
            "("
            "   id integer primary key,"
            "   username text,"
            "   password text,"
            "   phone text,"
            "   user_status integer DEFAULT 0"
            ");";
//            "insert into front_user"
//            "(username,password,phone)"
//            "values"
//            "('test','test','13112345678');";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_front_user:建表出错:%s\n",errmsg);
        return -1;
    }
//    printf("create_front_user:建表成功!\n");
    return 0;
}
//创建管理员账号表
int create_admin_user(){
    //定义建表语句
    const char * sql =
//            "drop table if EXISTS admin_user;"
            "create table if not exists admin_user"
            "("
            "   id integer primary key,"
            "   account text,"
            "   password text,"
            "   status text default 0"
            ");";
//            "insert into admin_user"
//            "(account,password)"
//            "values"
//            "('admin','admin');";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_admin_user:建表出错:%s\n",errmsg);
        return -1;
    }
//    printf("create_admin_user:建表成功!\n");
    return 0;
}
//创建图书分类表
int create_book_category(){
    //定义建表语句
    const char * sql =
//            "drop table if EXISTS book_category;"
            "create table if not exists book_category"
            "("
            "   category_id integer primary key autoincrement,"
            "   category_name text"
            ");";
//            "insert into book_category"
//            "(category_name)"
//            "values"
//            "('科幻'),('文艺'),('经济');";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_book_category:建表出错:%s\n",errmsg);
        return -1;
    }
//    printf("create_book_category:建表成功!\n");
    return 0;
}
//创建图书信息表
int create_book(){
    //定义建表语句
    const char * sql =
//            "drop table if EXISTS book;"
            "create table if not exists book"
            "("
            "   id integer primary key,"
            "   book_name text,"
            "   author text,"
            "   price real,"
            "   publish text,"
            "   publish_date text,"
            "   category_id integer,"
            "   stock integer"
            ");";
//            "insert into book "
//            "(book_name,author,price,publish,publish_date,category_id,stock) "
//            "values "
//            "('1566','张三',67,'上海出版社',date(),1,10),"
//            "('人民的名义','李四',85,'人民出版社',date(),2,20),"
//            "('狄仁杰','王五',32,'河南出版社',date(),3,30),"
//            "('python','张三',60,'郑州出版社',date(),1,10),"
//            "('C语言','李四',10,'周口出版社',date(),2,10);";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_book:建表出错:%s\n",errmsg);
        return -1;
    }
//    printf("create_book:建表成功!\n");
    return 0;
}

int creat_borrow_record(){
    const char * sql =""
//                      "drop table if EXISTS borrow_record;"
                      "CREATE TABLE IF NOT EXISTS borrow_record "
                      "    (record_id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 借阅记录唯一ID\n"
                      "    user_id INTEGER NOT NULL,                     -- 借阅用户ID\n"
                      "    book_name TEXT NOT NULL,                      -- 借阅图书名称\n"
                      "    borrow_time TIMESTAMP,                        -- 借阅时间\n"
                      "    due_time TIMESTAMP,                           -- 应还时间\n"
                      "    return_time TIMESTAMP,                        -- 归还时间\n"
                      "    status INTEGER DEFAULT 0            -- 状态：0-未还\n"
                      ");";
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_book_category:建表出错:%s\n",errmsg);
        return -1;
    }
//    printf("create_borrow_record:建表成功!\n");
    return 0;

};

//关闭数据库
int close_db(){
    if(db!=NULL){
        sqlite3_close(db);
        db=NULL;
    }
//    printf("close_db:关闭数据库成功!\n");
    return 0;
}
